﻿Imports Microsoft.VisualBasic
Imports System.Data

<System.ComponentModel.DataObjectAttribute(True)> _
Public Class BLL_SearchCleanPlan

    Public Sub New()
        ' 
        ' TODO: 在此加入建構函式的程式碼 
        ' 
    End Sub

    'SELECT 
    <System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.[Select])> _
    Public Function BLL_Select(ByVal UOrganizationID As String, ByVal VOrganizationID As String, ByVal Annual As String, ByVal PlanID As String, ByVal IsCleanPlanOnly As Boolean, ByVal PlanItemType As String, ByVal TakeStartDate As String, ByVal TakeEndDate As String, ByVal WorkDateStart As String, ByVal WorkDateEnd As String) As DataTable

        Dim sql As New MyOrganizationDataFilter(True)
        Dim dat As New clsData

        If IsCleanPlanOnly = False Then
            '執行成果
            TakeStartDate = Int32.Parse(TakeStartDate.Split(".")(0)) + 1911 & "." & Right(TakeStartDate, 6).Trim()
            TakeEndDate = Int32.Parse(TakeEndDate.Split(".")(0)) + 1911 & "." & Right(TakeEndDate, 6).Trim()
            WorkDateStart = Int32.Parse(WorkDateStart.Split(".")(0)) + 1911 & "." & Right(WorkDateStart, 6).Trim()
            WorkDateEnd = Int32.Parse(WorkDateEnd.Split(".")(0)) + 1911 & "." & Right(WorkDateEnd, 6).Trim()


            sql.AppendFormat("SELECT cm.Text as ItemTypeName,A.Active, A.LeaderID, A.DirectUOrganizationID, A.UOrganizationID, A.PlanID, A.PlanName, A.StartDate, A.EndDate, A.LeaderName, A.TakeDate, A.WorkStartDate, A.WorkEndDate, A.WorkHeaderID, IsPublic = 'N' ")
            sql.AppendFormat(", o.OrganizationName ")
            sql.AppendFormat(", vo.VOrganizationName as VOrganizationName ")
            sql.AppendFormat("FROM CleanPlanWorkHeaderView A ")
            sql.AppendFormat("inner join OrganizationView o on A.DirectUOrganizationID = o.UOrganizationID ")
            sql.AppendFormat("left join VOrganizationViewV2 vo on A.VUOrganizationID  = '-1'+','+cast(vo.VOrganizationID as nvarchar(50)) ")
            sql.AppendFormat(" left join CodeMapping cm on cm.Value = A.ItemType And cm.TableName='CleanPlan' And cm.ColumnName='ItemType' ")
            'sql.AppendFormat("LEFT JOIN Region B on A.VillageID=B.RegionID ")
            sql.AppendFormat("WHERE 1=1 ")

            If UOrganizationID <> "" Then
                sql.AppendFormat("and A.DirectUOrganizationID='{0}' ", UOrganizationID)
            End If

            If Annual <> "" Then
                sql.AppendFormat("AND year(A.TakeDate) = '{0}' ", Annual)
            End If

            If PlanID <> "" Then
                sql.AppendFormat("AND A.PlanID = '{0}' ", PlanID)
            End If

            'If clsAuth.GetCurrentPage.CanAdmin = False Then
            '    sql.AppendFormat("AND getdate() between A.StartDate and A.EndDate ")
            'End If

            If PlanItemType <> "" Then
                sql.AppendFormat(" AND A.ItemType = '{0}' ", PlanItemType)
            End If

            If VOrganizationID <> "" Then
                sql.AppendFormat("and vo.VOrganizationID={0} ", VOrganizationID)
            End If
            If TakeStartDate <> "1912.01.01" AndAlso TakeEndDate <> "1912.01.02" Then
                sql.AppendFormat("and A.TakeDate between '{0:yyyy/MM/dd}' and '{1:yyyy/MM/dd}' ", DateTime.Parse(TakeStartDate).ToString("yyyy/MM/dd"), DateTime.Parse(TakeEndDate).ToString("yyyy/MM/dd"))
            ElseIf TakeStartDate <> "1912.01.01" AndAlso TakeEndDate = "1912.01.02" Then '只單選前一個月份
                sql.AppendFormat("and A.TakeDate >= '{0:yyyy/MM/dd}' ", DateTime.Parse(TakeStartDate).ToString("yyyy/MM/dd"))
            ElseIf TakeStartDate = "1912.01.01" AndAlso TakeEndDate <> "1912.01.02" Then '只單選後一個月份
                sql.AppendFormat("and A.TakeDate < '{0:yyyy/MM/dd}' ", DateTime.Parse(TakeEndDate).AddDays(-1).AddMonths(1).ToString("yyyy/MM/dd"))

            End If

            If WorkDateStart <> "1912.01.01" AndAlso WorkDateEnd <> "1912.01.02" Then
                sql.AppendFormat("and A.WorkStartDate between '{0:yyyy/MM/dd}' and '{1:yyyy/MM/dd}' ", DateTime.Parse(WorkDateStart).ToString("yyyy/MM/dd"), DateTime.Parse(WorkDateEnd).ToString("yyyy/MM/dd"))
            ElseIf WorkDateStart <> "1912.01.01" AndAlso WorkDateEnd = "1912.01.02" Then '只單選前一個月份
                sql.AppendFormat("and A.WorkStartDate >= '{0:yyyy/MM/dd}' ", DateTime.Parse(WorkDateStart).ToString("yyyy/MM/dd"))
            ElseIf WorkDateStart = "1912.01.01" AndAlso WorkDateEnd <> "1912.01.02" Then '只單選後一個月份
                sql.AppendFormat("and A.WorkStartDate < '{0:yyyy/MM/dd}' ", DateTime.Parse(WorkDateEnd).AddDays(-1).AddMonths(1).ToString("yyyy/MM/dd"))

            End If
      

            sql.AppendFormat("union ")
        End If

        '計畫
        'sql.AppendFormat("insert #a (PlanID, PlanName, StartDate, EndDate ")
        'sql.AppendFormat(", OrganizationName) select ")

        sql.AppendFormat("select cm.Text as ItemTypeName,Active = null, LeaderID = null, DirectUOrganizationID = c.UOrganizationID, c.UOrganizationID, PlanID, PlanName, StartDate, EndDate ")
        sql.AppendFormat(", LeaderName = null, TakeDate = null, WorkStartDate = null, WorkEndDate = null, WorkHeaderID = null, c.IsPublic ")
        sql.AppendFormat(", OrganizationName ")
        sql.AppendFormat(", VOrganizationName = null ")
        sql.AppendFormat("from CleanPlan c ")
        sql.AppendFormat("inner join OrganizationView o on c.UOrganizationID = o.UOrganizationID ")
        sql.AppendFormat(" left join CodeMapping cm on cm.Value = c.ItemType And cm.TableName='CleanPlan' And cm.ColumnName='ItemType' ")
        sql.AppendFormat("WHERE 1=1 ")

        If UOrganizationID <> "" Then
            sql.AppendFormat("and c.UOrganizationID='{0}' ", UOrganizationID)
        End If

        If Annual <> "" Then
            sql.AppendFormat("AND year(c.StartDate) = '{0}' ", Annual)
        End If

        If PlanID <> "" Then
            sql.AppendFormat("AND c.PlanID = '{0}' ", PlanID)
        End If

        'If clsAuth.GetCurrentPage.CanAdmin = False Then
        '    sql.AppendFormat("AND getdate() between c.StartDate and c.EndDate ")
        'End If

        If PlanItemType <> "" Then
            sql.AppendFormat(" AND c.ItemType = '{0}' ", PlanItemType)
        End If

        '

        If IsCleanPlanOnly = False Then
            sql.AppendFormat("  order by PlanID,TakeDate,LeaderName  ")
        Else
            sql.AppendFormat("  order by PlanID ")
        End If
        ' sql.AppendFormat("order by DirectUOrganizationID, StartDate desc, TakeDate ")
        '  MyObj.WriteErrorLog(sql.ToString())

        Return dat.GetData(sql)

    End Function

    'Delete 
    <System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Delete)> _
    Public Sub BLL_Delete(ByVal original_PlanID As Integer, ByVal original_WorkHeaderID As Integer)

        Dim dat As New clsData
        Dim sql As New MyStringBuilder

        Using scope As New Transactions.TransactionScope

            sql.Length = 0

            sql.AppendFormat("DELETE whr from WorkHourRecord whr inner join WorkRecord wr on whr.RecordID = wr.RecordID and whr.WorkType='CleanPlan' WHERE wr.WorkHeaderID={0} ", original_WorkHeaderID)
            sql.AppendFormat("DELETE WorkRecord WHERE WorkHeaderID = {0}", original_WorkHeaderID)
            sql.AppendFormat("DELETE WorkHeader WHERE WorkHeaderID = {0}", original_WorkHeaderID) '可省略PlanID因為WorkHeaderID在WorkHeader不重複
            sql.AppendFormat("DELETE CleanPlanMapping WHERE PlanID = {0} and WorkHeaderID = {1}", original_PlanID, original_WorkHeaderID)

            dat.AccData(sql)

            scope.Complete()

        End Using

    End Sub

End Class
